using System;
using System.Data;
using System.Data.SqlClient; 
//using System.Data.SqlTypes;
using System.Diagnostics;


namespace FilmekAdatbazis
{
	/// <summary>
	/// Summary description for Aru.
	/// </summary>
	public class kolcson: DAL
	{
		//		private System.Data.SqlClient.SqlCommand myUdateComm;
		private System.Data.SqlClient.SqlDataAdapter daGrid;
		private System.Data.SqlClient.SqlDataAdapter daAruk; 
		private System.Data.DataSet dsArukGrid;
		private System.Data.DataSet dsAruk;
		private System.Data.DataSet dsEgyAruSor;
		private System.Data.SqlClient.SqlDataAdapter daEgyAruSor;
		//private string ErrMess;
		private string sQuery;
		public kolcson(ref bool Err)
		{
			if (!base.IsConnectCreated())
			{
				Err = !base.MakeConnection();
			}
			daAruk = new SqlDataAdapter();
			daAruk.TableMappings.Add("Table", "Kolcsonzesek");
			daEgyAruSor = new SqlDataAdapter();
			daEgyAruSor.TableMappings.Add("Table", "EgyAru");
			//			dsArukGrid = new DataSet("ArukGrid");
			daGrid = new SqlDataAdapter();
			daGrid.TableMappings.Add("Table", "ArukGrid");
		}
	
		public System.Data.DataSet AruGridTolt(string sBetuk, ref string ErrM)
		{
			string sSQL = "SELECT K.KolcsonzID,F.FelhasznaloNev,K.Mikortol,K.Meddig,K.Raktarszam,K.Vissza FROM Kolcsonzesek K, Felhasznalok F WHERE K.FelhasznaloID=F.FelhasznaloID and F.FelhasznaloID LIKE '" + sBetuk + "%'";
			dsArukGrid = base.myFillDataSetFromString(sSQL,"ArukGrid",ref daGrid,ref ErrM);
			return dsArukGrid;
			
		}

        public System.Data.SqlClient.SqlDataReader getFelhasznalok(ref String ErrM)
        {
            myDataReader = base.myExecuteReader("SELECT FelhasznaloNev FROM Felhasznalok", ref ErrM);
            return myDataReader;
        }

		public System.Data.DataSet ArukTolt(ref string ErrM)
		{
			string sSQL = "SELECT * FROM Kolcsonzesek";
			dsAruk = base.myFillDataSetFromString(sSQL,"Aruk",ref daAruk,ref ErrM);
			return dsAruk;
			
		}
		public System.Data.DataSet AruEgySorTolt(string sArukod, ref string ErrM)
		{
			String sSQL = "SELECT * FROM Kolcsonzesek WHERE KolcsonzID="+sArukod;

			dsEgyAruSor = base.myFillDataSetFromString(sSQL,"EgyAru", ref daEgyAruSor,ref ErrM);
			return dsEgyAruSor;
			
									
		}
		public void EgySorModosit(System.Data.DataRow drEgySor,	ref string Err)
		{
			base.OpenConnection();
			daEgyAruSor.UpdateCommand = new SqlCommand("UPDATE Kolcsonzesek SET FelhasznaloID = @FelhasznaloID " +
				", Mikortol = @Mikortol , Meddig = @Meddig , Raktarszam= @Raktarszam , Vissza= @Vissza "+
				"WHERE KolcsonzID = @KolcsonzID" ,base.theConnection());
			daEgyAruSor.UpdateCommand.Parameters.Add("@FelhasznaloID", SqlDbType.Int, 4, "FelhasznaloID");
			daEgyAruSor.UpdateCommand.Parameters.Add("@Mikortol", SqlDbType.DateTime, 40, "Mikortol");
			daEgyAruSor.UpdateCommand.Parameters.Add("@Meddig", SqlDbType.DateTime, 8, "Meddig");
            daEgyAruSor.UpdateCommand.Parameters.Add("@Raktarszam", SqlDbType.Int, 4, "Raktarszam");
            daEgyAruSor.UpdateCommand.Parameters.Add("@Vissza", SqlDbType.Char, 1, "Vissza");
            SqlParameter workParm = daEgyAruSor.UpdateCommand.Parameters.Add("@KolcsonzID", SqlDbType.Int);
			workParm.SourceColumn = "KolcsonzID";
			//workParm.SourceVersion = DataRowVersion.Original;
			try
			{
				daEgyAruSor.Update(dsEgyAruSor,"EgyAru");
				Err = "OK";
			}
			catch( DBConcurrencyException ex )
			{
				Err = ex.ToString();
				dsEgyAruSor.RejectChanges();
			}
							
			base.CloseConnection();
			
		}
		public System.Data.DataSet RefreshAruGrid(ref string Err)
		{
			base.OpenConnection();
			try
			{
				dsArukGrid.Clear();
				daGrid.Fill(dsArukGrid);				
				Err = "OK";
				return dsArukGrid;
			}
			catch( Exception ex )
			{
				Err = ex.ToString();
				return null;
			}
			finally
			{
				base.CloseConnection();
			}
		}
		public int MaxAruKod(ref string Err)
		{
			sQuery = "SELECT MAX(KolcsonzID) FROM Kolcsonzesek";
			int iKod = base.myExecuteScalar(sQuery, ref Err);
			return iKod;
		}

        public int getFelhasznaloID(string fNev ,ref string Err)
        {
            sQuery = "SELECT FelhasznaloID FROM Felhasznalok f WHERE f.FelhasznaloNev = "+fNev;
            int kapottID = base.myExecuteScalar(sQuery, ref Err);
            return kapottID;
        }

        public int MaxSzamlaElemID(ref string Err)
        {
            sQuery = "SELECT MAX(SzamlaID) FROM Szamla_elemek";
            int iKod = base.myExecuteScalar(sQuery, ref Err);
            return iKod;
        }

        public void inditTranzakcio(ref string Err)
        {
            sQuery = "BEGIN TRANSACTION";
            base.myExecuteScalar(sQuery, ref Err);
        }

        public void commitTrans(ref string Err)
        {
            sQuery = "COMMIT TRANSACTION";
            base.myExecuteScalar(sQuery, ref Err);
        }

        public void anulal(ref string ErrM)
        {
            string sSQL = "ROLLBACK TRANSACTION";
            base.myExecuteScalar(sSQL, ref ErrM);
        }

        public void addKosar(int ID,int kod, ref string Err)
        {
            //ID++;
            sQuery = "INSERT INTO Szamla_elemek VALUES ("+ID+","+kod+")";
            base.myExecuteScalar(sQuery, ref Err);
        }

				public void InsertParancs()
					//int iKod, string sNev, int iCsopKod, Double MennyRakt,
					//ref string Err)
				{
					
					daAruk.InsertCommand = new SqlCommand("INSERT INTO Kolcsonzesek VALUES (?, ?, ?, ?, ?, ?) ");
                    daAruk.InsertCommand.Parameters.Add("@FelhasznaloID", SqlDbType.Int, 8, "FelhasznaloID");
					daAruk.InsertCommand.Parameters.Add("@Mikortol", SqlDbType.VarChar, 40,"Mikortol");
					daAruk.InsertCommand.Parameters.Add("@Meddig", SqlDbType.VarChar, 8, "Meddig");
					daAruk.InsertCommand.Parameters.Add("@Raktarszam", SqlDbType.Int, 8, "Raktarszam");
                    daAruk.InsertCommand.Parameters.Add("@Vissza", SqlDbType.Char, 1, "Vissza");
                    daAruk.InsertCommand.Parameters.Add("@KolcsonzID", SqlDbType.Int, 8, "KolcsonzID");



                    daAruk.InsertCommand.Connection = base.theConnection();	
//					base.OpenConnection();
//					try
//					{
//						daAruk.Fill(dsAruk);
//						DataRow drAruSor = dsAruk.Tables["Aruk"].NewRow();
//						drAruSor.ItemArray = new object[] {iKod, sNev,  iCsopKod, MennyRakt};
//						dsAruk.Tables["Aruk"].Rows.Add(drAruSor);
//						daAruk.Update( dsAruk, "Aruk");
//						Err = "OK";
//					}
//					catch( Exception ex )
//					{
//						Err = ex.ToString();
//						dsEgyAruSor.RejectChanges();
//					}
//					base.CloseConnection();
				}
				
		public void UjSorIr(int iKod, string smikor, string smeddig, string rakszam, char c, 
			ref string Err)
		{
            int kolcsid = MaxAruKod(ref Err)+1;
			sQuery = "INSERT INTO Kolcsonzesek VALUES ("+Convert.ToString(kolcsid)+","+
				Convert.ToString(iKod) + "," + "'" + smikor + "'" + ",'" +smeddig+"',"+rakszam+",'"+c+"')";
			base.myInsertCommand(sQuery,daAruk,ref Err);
			
		}
		public void TorolEgySort(int iKod, ref string Err)
		{
			sQuery = "DELETE FROM Kolcsonzesek WHERE KolcsonzID = " +Convert.ToString(iKod);
			MyDeleteComm(sQuery,daEgyAruSor,ref Err);
		}



		public void WriteDataSetInDB(DataSet dsAruk, ref string Err)
		{
			InsertParancs();
			DataTable NewChildRecords = dsAruk.Tables["Kolcsonzesek"].GetChanges(DataRowState.Added);
			DataTable DeletedChildRecords = dsAruk.Tables["Kolcsonzesek"].GetChanges(DataRowState.Deleted);
			//workTable.PrimaryKey = new DataColumn[] {workTable.Columns["CustID"]};

			dsAruk.Tables["Kolcsonzesek"].PrimaryKey= new DataColumn [] {dsAruk.Tables["Kolcsonzesek"].Columns["Kolcsonzesek"]};
			try
			{
				if (DeletedChildRecords != null)
				{
					daAruk.Update(DeletedChildRecords);
					DeletedChildRecords.Dispose();
				}
				if (NewChildRecords != null)
				{
			//		daAruk.Update(NewChildRecords);
					daAruk.Update(dsAruk,"Aruk");
					NewChildRecords.Dispose();
				}
			}
			catch(Exception ex)
			{
				Err = ex.ToString();
				dsAruk.RejectChanges();
				
			}
		}
	}
}
